def clean_data(df_path):
"""
Perform feature trimming, re-encoding, and engineering for demographics
data
INPUT: Demographics DataFrame
OUTPUT: Trimmed and cleaned demographics DataFrame
"""
# load data
df = pd.read_csv(df_path/'Udacity_AZDIAS_Subset.csv', delimiter=';')
feat_info = pd.read_csv(df_path/'AZDIAS_Feature_Summary.csv', delimiter=';')
print("Original dataset shape: {}".format(df.shape))
## create lists for missing value types
for i in range(df.shape[1]):
col_name = df.columns[i]
missing_list = feat_info.iloc[i, 3]
missing_list = missing_list.strip('[]').split(',')
if missing_list == ['']: continue
else:
for mc in missing_list:
if df[col_name].dtype=='object':
df.loc[df[col_name]==mc, col_name] = np.nan
else:
mc = int(mc)
df.loc[df[col_name]==mc, col_name] = np.nan
## remove the columns which have more than 30% of missing values
# create a dataframe for missing value percent
missing_cols_df = pd.DataFrame((df.isnull().sum()/len(df)*100), columns=['percent'])
missing_threshold = 20 # set up the threshold for dropping columns
drop_missing_cols = missing_cols_df[missing_cols_df['percent']>20].index
print("\nI will drop columns because they have more than 20% of missing values in columns:\n", drop_missing_cols.tolist())
df = df.drop(drop_missing_cols, axis=1)
print("\nDataset after dropped columns which have more than 20% of missing values: ", df.shape)
## remove the rows which have more than 30% of missing values
missing_rows = df[df.isnull().sum(axis=1)>30]
df = df.drop(missing_rows.index, axis=0)
print("\nDataset after dropped rows which have more than 30% of missing values: ", df.shape)
## re-encode "OST_WEST_KZ" value (W: west, O: east) as 1, 2
df['OST_WEST_KZ'] = df['OST_WEST_KZ'].map({'O':1, 'W':2})
## feature engineering for PRAEGENDE_JUGENDJAHRE
# create a column for mainstream with value 0, 1
mainstream = [1.0, 3.0, 5.0, 8.0, 10., 12.0, 14.0]
df['MAINSTREAM_AVANTGARDE'] = np.where(df['PRAEGENDE_JUGENDJAHRE'].isin(mainstream), 0, 1)
# create a dictionary for decade information from PRAEGENDE_JUGENDJAHRE
decade = {1.0: 1940, 2.0: 1940,
3.0: 1950, 4.0: 1950,
5.0: 1960, 6.0: 1960, 7.0: 1960,
8.0: 1970, 9.0: 1970,
10.0: 1980, 11.0: 1980, 12.0: 1980, 13.0: 1980,
14.0: 1990, 15.0: 1990}
df['DACADE'] = df['PRAEGENDE_JUGENDJAHRE'].map(decade)
## feature engineering for CAMEO_INTL_2015
# change CAMEO_INTL_2015's dtypes to float
df['CAMEO_INTL_2015'] = df['CAMEO_INTL_2015'].astype(float)
# Create a new variable: WEALTH
df.loc[df['CAMEO_INTL_2015'].isin([51,52,53,54,55]),'WEALTH']=1
df.loc[df['CAMEO_INTL_2015'].isin([41,42,43,44,45]),'WEALTH']=2
df.loc[df['CAMEO_INTL_2015'].isin([31,32,33,34,35]),'WEALTH']=3
df.loc[df['CAMEO_INTL_2015'].isin([21,22,23,24,25]),'WEALTH']=4
df.loc[df['CAMEO_INTL_2015'].isin([11,12,13,14,15]),'WEALTH']=5
# Create a new variable: LIFE_STAGE
df.loc[df['CAMEO_INTL_2015'].isin([11,21,31,41,51]),'LIFE_STAGE']=1
df.loc[df['CAMEO_INTL_2015'].isin([12,22,32,42,52]),'LIFE_STAGE']=2
df.loc[df['CAMEO_INTL_2015'].isin([13,23,33,43,53]),'LIFE_STAGE']=3
df.loc[df['CAMEO_INTL_2015'].isin([14,24,34,44,54]),'LIFE_STAGE']=4
df.loc[df['CAMEO_INTL_2015'].isin([15,25,35,45,55]),'LIFE_STAGE']=5
## Drop reduntant columns
drop_cols = ['LP_FAMILIE_FEIN', 'LP_STATUS_FEIN', 'CAMEO_DEU_2015', 'CAMEO_DEUG_2015',
'LP_LEBENSPHASE_FEIN', 'PRAEGENDE_JUGENDJAHRE', 'CAMEO_INTL_2015',
'KBA05_ANTG1', 'KBA05_ANTG2', 'KBA05_ANTG3', 'KBA05_ANTG4',
'PLZ8_ANTG1', 'PLZ8_ANTG2', 'PLZ8_ANTG3', 'PLZ8_ANTG4',
'LP_LEBENSPHASE_GROB', 'GFK_URLAUBERTYP','GEBAEUDETYP', 'WOHNLAGE']
df = df.drop(drop_cols, axis=1)
print("\nDropped {} redundant columns".format(len(drop_cols)))
print("\nFinal dataset's shape: ", df.shape)
## Return the cleaned dataframe.
return df, feat_info
